using System;
using System.IO;
using System.Text;
using System.ComponentModel;
using CodeSmith.Engine;
using System.Data;
using SchemaExplorer;
using CodeSmith.CustomProperties;
using System.Text.RegularExpressions;

namespace CodeSmith.MyBaseTemplates
{
	public class SqlCodeTemplate : CodeTemplate
	{			
		#region Properties
		// Namespace.
		private string _Namespace = "Components";
		[Category("Class")]
		[Description("Namespace.")]
		public string Namespace
		{ 
			get {return this._Namespace;}
			set {_Namespace = value;}			
		}

		// Source table.
		private TableSchema _SourceTable;
		[Category("Database")]
		[Description("Source table.")]
		public TableSchema SourceTable
		{ 
			get {return this._SourceTable;}
			set {_SourceTable = value;}			
		}
		// Table prefix.
		private string _TablePrefix = "t_";
		[Category("Database")]
		[Description("Table prefix.")]
		public string TablePrefix
		{ 
			get {return this._TablePrefix;}
			set {_TablePrefix = value;}			
		}
		// Stored procedure prefix.
		private string _StoredProcedurePrefix = "p_";
		[Category("Database")]
		[Description("Stored procedured prefix.")]
		public string StoredProcedurePrefix
		{ 
			get {return this._StoredProcedurePrefix;}
			set {_StoredProcedurePrefix = value;}			
		}
		// Schema name.
		private string _Schema = "dbo";
		[Category("Database")]
		[Description("Schema.")]
		public string Schema
		{ 
			get {return this._Schema;}
			set {_Schema = value;}			
		}
		
		// Excluded columns.
		private StringCollection _ExcludedColumns;
		[Category("Database")]
		[Description("Excluded Columns.")]
		[Optional()]
		public StringCollection ExcludedColumns
		{ 
			get {return this._ExcludedColumns;}
			set {_ExcludedColumns = value;}
		}
		
		// Included collection.
		private bool _IncludedCollection;
		[Category("Database")]
		[Description("Included Collection.")]
		[Optional()]
		public bool IncludedCollection
		{ 
			get {return this._IncludedCollection;}
			set {_IncludedCollection = value;}
		}
		
		// ReadOnly columns.
		private StringCollection _ReadOnlyColumns;
		[Category("Database")]
		[Description("ReadOnly Columns.")]
		[Optional()]
		public StringCollection ReadOnlyColumns
		{ 
			get {return this._ReadOnlyColumns;}
			set {_ReadOnlyColumns = value;}
		}
		
        // IncludeMaDonVi.
		private bool _IncludeMaDonVi = false;
		[Category("Options")]
		[Description("Bao gồm tham số MaDonVi trong các methods và stores")]
        [Optional()]
		public bool IncludeMaDonVi
		{ 
			get {return this._IncludeMaDonVi;}
			set {_IncludeMaDonVi = value;}			
		}
        
		#endregion
		
		#region ColumnIsExcluded
		
		private Regex excludedColumnRegex = null;
		public bool ColumnIsExcluded(ColumnSchema column)
		{
			if (column.IsPrimaryKeyMember) return false;
			
			if (excludedColumnRegex == null)
			{
				if (ExcludedColumns != null && ExcludedColumns.Count > 0)
				{
					string excluded = String.Empty;
					for (int i = 0; i < ExcludedColumns.Count; i++)
					{
						if (ExcludedColumns[i].Trim().Length > 0)
						{
							excluded += "(" + Regex.Escape(ExcludedColumns[i]).Replace("\\*", ".*?") + ")|";
						}
					}
					
					if (excluded.Length > 0)
					{
						excluded = excluded.Substring(0, excluded.Length - 1);
						excludedColumnRegex = new Regex(excluded, RegexOptions.IgnoreCase);
					}
				}
			}
			
			if (excludedColumnRegex != null && excludedColumnRegex.IsMatch(column.Name)) return true;
			
			return false;
		}
		
		public ColumnSchemaCollection FilterExcludedColumns(ColumnSchemaCollection columns)
		{
			ColumnSchemaCollection filteredColumns = new ColumnSchemaCollection();
			
			for (int i = 0; i < columns.Count; i++)
			{
				if (!ColumnIsExcluded(columns[i])) 
					filteredColumns.Add(columns[i]);
			}
			
			return filteredColumns;
		}
		
		#endregion
		
		#region ColumnIsReadOnly
		
		private Regex readOnlyColumnRegex = null;
		public bool ColumnIsReadOnly(ColumnSchema column)
		{
			if (column.IsPrimaryKeyMember) return false;
			
			if (readOnlyColumnRegex == null)
			{
				if (ReadOnlyColumns != null && ReadOnlyColumns.Count > 0)
				{
					string readOnly = String.Empty;
					for (int i = 0; i < ReadOnlyColumns.Count; i++)
					{
						if (ReadOnlyColumns[i].Trim().Length > 0)
						{
							readOnly += "(" + Regex.Escape(ReadOnlyColumns[i]).Replace("\\*", ".*?") + ")|";
						}
					}
					
					if (readOnly.Length > 0)
					{
						readOnly = readOnly.Substring(0, readOnly.Length - 1);
						readOnlyColumnRegex = new Regex(readOnly, RegexOptions.IgnoreCase);
					}
				}
			}
			
			if (readOnlyColumnRegex != null && readOnlyColumnRegex.IsMatch(column.Name)) return true;
			
			return false;
		}
		
		#endregion
		
		public bool ColumnIsExcludedOrReadOnly(ColumnSchema column)
		{
			return ColumnIsExcluded(column) || ColumnIsReadOnly(column);
		}
		
		#region ModuleID
		
		public string DeclareModuleID()
		{
			foreach(ColumnSchema col in SourceTable.Columns)
			{
				if (col.Name == "ModuleID")
				{
					return "Guid moduleID";
				}
			}
			return "";
		}

		public string DeclareModuleID2()
		{
			foreach(ColumnSchema col in SourceTable.Columns)
			{
				if (col.Name == "ModuleID")
				{
					return "moduleID";
				}
			}
			return "";
		}

		public string SetParameterModuleID()
		{
			foreach(ColumnSchema col in SourceTable.Columns)
			{
				if (col.Name == "ModuleID")
				{
					return "moduleID";
				}
			}
			return "";
		}
		public string ParameterModuleID()
		{
			foreach(ColumnSchema col in SourceTable.Columns)
			{
				if (col.Name == "ModuleID")
				{
					return "db.AddInParameter(dbCommand, \"@ModuleID\", SqlDbType.UniqueIdentifier, moduleID);";
				}
			}
			return "";
		}

		#endregion

        public string GetParametersIncludeMaDonVi()
        {
            return (IncludeMaDonVi ? ", System.Guid maDonVi" : "");
        }
        
        public string GetCommandIncludeMaDonVi()
        {
            return (IncludeMaDonVi ? " && item.MaDonVi == maDonVi" : "");
        }
        
		public string GetParametersInsertCommand(ColumnSchemaCollection columns)
		{
			System.Text.StringBuilder bySuffix = new System.Text.StringBuilder();
			bool isHasAutonumberPK = false;			
			for (int i = 0; i < columns.Count; i++)
			{
				if(columns[i].IsPrimaryKeyMember)
				{	
                    isHasAutonumberPK = ColumnIsIdentity(columns[i]);
                    
					if(!isHasAutonumberPK)
					{
						bySuffix.Append("@" + columns[i].Name);
						if (i < columns.Count - 1)
							bySuffix.Append(", ");
					}
				}
				else
				{
					bySuffix.Append("@" + columns[i].Name);
					if (i < columns.Count - 1)
						bySuffix.Append(", ");
				}								
			} 
            
            string result = bySuffix.ToString().Trim();
//			if (result.LastIndexOf(",") > 0)
//                    result = result.Substring(0, result.LastIndexOf(","));
			
            return result;
		}

		public string GetParametersUpdateCommand(ColumnSchemaCollection columns)
		{
			System.Text.StringBuilder bySuffix = new System.Text.StringBuilder();
			for (int i = 0; i < columns.Count; i++)
			{
				if(!ColumnIsIdentity(columns[i]))
				{						
					bySuffix.Append(columns[i].Name + " = @" + columns[i].Name);
					if (i < columns.Count - 1)
						bySuffix.Append(", ");
				}								
			}
			 
            string result = bySuffix.ToString().Trim();
//			if (result.LastIndexOf(",") > 0)
//                    result = result.Substring(0, result.LastIndexOf(","));
			
            return result;
		}

		public string GetBySuffix(ColumnSchemaCollection columns)
		{
			System.Text.StringBuilder bySuffix = new System.Text.StringBuilder();
			for (int i = 0; i < columns.Count; i++)
			{
				if (i > 0) bySuffix.Append("_");
				bySuffix.Append(columns[i].Name);
			}
			
			return bySuffix.ToString();
		}

		public string GetCSharpParamsBySuffix(ColumnSchemaCollection columns)
		{
			System.Text.StringBuilder bySuffix = new System.Text.StringBuilder();
			for (int i = 0; i < columns.Count; i++)
			{
//				if (columns[i].Name.ToUpper().Equals("ID"))
//				{
//					bySuffix.Append(GetCSharpVariableType(columns[i]) + " " + columns[i].Name.ToLower());
//				}
//				else
//				{
					bySuffix.Append(GetCSharpVariableType(columns[i]) + " " + columns[i].Name.Substring(0, 1).ToLower() + columns[i].Name.Substring(1));
//				}
				if (i < columns.Count - 1)
				{
					bySuffix.Append(", ");
				}
			}
			
			return bySuffix.ToString();
		}
		
		public string GetCSharpParamsBySuffixIdentity(ColumnSchemaCollection columns)
		{
			System.Text.StringBuilder bySuffix = new System.Text.StringBuilder();
			for (int i = 0; i < columns.Count; i++)
			{
				if (columns[i].IsPrimaryKeyMember && ColumnIsIdentity(columns[i]))
				{
					bySuffix.Append(GetCSharpVariableType(columns[i]) + " " + columns[i].Name.Substring(0, 1).ToLower() + columns[i].Name.Substring(1));
				}				
			}
			
			return bySuffix.ToString();
		}
        		
		public string GetCSharpParamsBySuffixExcludeIdentity(ColumnSchemaCollection columns)
		{
			System.Text.StringBuilder bySuffix = new System.Text.StringBuilder();
			for (int i = 0; i < columns.Count; i++)
			{
				if (!ColumnIsIdentity(columns[i]))
				{
					bySuffix.Append(GetCSharpVariableType(columns[i]) + " " + columns[i].Name.Substring(0, 1).ToLower() + columns[i].Name.Substring(1));
				}
                
                if (i < columns.Count - 1)
					bySuffix.Append(", ");
			}
			
			string result = bySuffix.ToString().Trim();
			if (result.LastIndexOf(",") > 0)
                    result = result.Substring(0, result.LastIndexOf(","));
			
            return result;
		}
        
		public string GetCSharpParamsBySuffix2(ColumnSchemaCollection columns)
		{
			string bySuffix = "";
			for (int i = 0; i < columns.Count; i++)
			{
				if(columns[i].IsPrimaryKeyMember && columns[i].IsUnique)
				{
//					if (columns[i].Name.ToUpper().Equals("ID"))
//					{
//						bySuffix = columns[i].Name.ToLower();
//						break;
//					}
//					else
//					{
						bySuffix = columns[i].Name.Substring(0, 1).ToLower() + columns[i].Name.Substring(1);
						break;
//					}
				}
			}
			
			return bySuffix;
		}

		public string GetCSharpInputParamsBySuffix(ColumnSchemaCollection columns)
		{
			System.Text.StringBuilder bySuffix = new System.Text.StringBuilder();
			for (int i = 0; i < columns.Count; i++)
			{
				bySuffix.Append(columns[i].Name.Substring(0, 1).ToLower() + columns[i].Name.Substring(1));
					if (i < columns.Count - 1)
						bySuffix.Append(", ");
			}
			
			return bySuffix.ToString();
		}

		public string GetCSharpParamsBySuffixExceptUnique(ColumnSchemaCollection columns)
		{
			System.Text.StringBuilder bySuffix = new System.Text.StringBuilder();
            bool isBegin = false;
			for (int i = 0; i < columns.Count; i++)
			{
				if (columns[i].IsUnique == false)
				{
					/*
					if (columns[i].AllowDBNull)
						bySuffix.Append(GetCSharpVariableType(columns[i]) + "? " + columns[i].Name.Substring(0, 1).ToLower() + columns[i].Name.Substring(1));
					else
					*/
						bySuffix.Append(GetCSharpVariableType(columns[i]) + " " + columns[i].Name.Substring(0, 1).ToLower() + columns[i].Name.Substring(1));
                        isBegin = true;
				}
                else
            	{
            	    if(!IsAutoNumberPrimaryKeyColumn(columns[i]))
                    {
                        bySuffix.Append(GetCSharpVariableType(columns[i]) + " " + columns[i].Name.Substring(0, 1).ToLower() + columns[i].Name.Substring(1));
                        isBegin = true;
                    }
            	}
					
                if (isBegin && i < columns.Count - 1)
					bySuffix.Append(", ");
            }
			
			return bySuffix.ToString();
		}

		//-----------------------------------------------------------------------------------------
		public string GetMemberVariableDeclarationStatement(ColumnSchema column)
		{
			return GetMemberVariableDeclarationStatement("protected", column);
		}
		
		public string GetMemberVariableDeclarationStatement(string protectionLevel, ColumnSchema column)
		{
			string statement = protectionLevel + " ";
			/*
			if (column.AllowDBNull)
				statement += GetCSharpVariableType(column) + "? " + GetMemberVariableName(column);
			else
			*/
				statement += GetCSharpVariableType(column) + " " + GetMemberVariableName(column);
			
			string defaultValue = GetMemberVariableDefaultValue(column);
			if (defaultValue != "")
			{
				statement += " = " + defaultValue;
			}
			
			statement += ";";
			
			return statement;
		}
		
		/*
		public string GetReaderAssignmentStatement(ColumnSchema column, int index)
		{
			string statement = "if (!reader.IsDBNull(" + index.ToString() + ")) ";
			statement += GetMemberVariableName(column) + " = ";
			
			if (column.Name.EndsWith("TypeCode")) statement += "(" + column.Name + ")";
			
			statement += "reader." + GetReaderMethod(column) + "(" + index.ToString() + ");";
			
			return statement;
		}
		*/
		
		public string GetReaderAssignmentStatement(ColumnSchema column)
		{
			string statement = "if (!reader.IsDBNull(reader.GetOrdinal(\"" + column.Name + "\"))) ";
			statement += "this." + GetMemberVariableName(column) + " = ";
			
			if (column.Name.EndsWith("TypeCode")) statement += "(" + column.Name + ")";
			
			statement += "reader." + GetReaderMethod(column) + "(" + "reader.GetOrdinal(\"" + column.Name + "\")" + ");";
			
			return statement;
		}

		public string GetReaderAssignmentStatement2(ColumnSchema column)
		{
			string statement = "if (!reader.IsDBNull(reader.GetOrdinal(\"" + column.Name + "\"))) ";
			statement += "entity." + column.Name + " = ";
			
			if (column.Name.EndsWith("TypeCode")) statement += "(" + column.Name + ")";
			
            if (column.DataType == DbType.Binary)
                statement += "(byte[])" + "reader[\"" + column.Name + "\"];";
            else    
			    statement += "reader." + GetReaderMethod(column) + "(" + "reader.GetOrdinal(\"" + column.Name + "\")" + ");";
			
			return statement;
		}
		
		public string GetCamelCaseName(string value)
		{
			return value.Substring(0, 1).ToLower() + value.Substring(1);
			//return value;
		}
		
		public string GetMemberVariableName(ColumnSchema column)
		{
			string propertyName = GetPropertyName(column);
			string memberVariableName = "_" + GetCamelCaseName(propertyName);
			
			return memberVariableName;
		}
		
		public string GetPropertyName(ColumnSchema column)
		{
			string propertyName = column.Name;
			
			if (propertyName == column.Table.Name + "Name") return "Name";
			if (propertyName == column.Table.Name + "Description") return "Description";
			
			if (propertyName.EndsWith("TypeCode")) propertyName = propertyName.Substring(0, propertyName.Length - 4);
			
			return propertyName;
		}
				
		public string GetMemberVariableDefaultValue(ColumnSchema column)
		{
			switch (column.DataType)
			{
				case DbType.DateTime:
				{
					return "new DateTime(1753, 1, 1)";
				}
				case DbType.Guid:
				{
					return "Guid.Empty";
				}
				case DbType.AnsiString:
				case DbType.AnsiStringFixedLength:
				case DbType.String:
				case DbType.StringFixedLength:
				{
					return "string.Empty";
				}
				default:
				{
					return "";
				}
			}
		}
		
		public string GetCSharpVariableType(ColumnSchema column)
		{
			if (column.Name.EndsWith("TypeCode")) return column.Name;
			
			switch (column.DataType)
			{
				case DbType.AnsiString: return "string";
				case DbType.AnsiStringFixedLength: return "string";
				case DbType.Binary: return "byte[]";
				case DbType.Boolean: return "bool";
				case DbType.Byte: return "byte";
				case DbType.Currency: return "decimal";
				case DbType.Date: return "DateTime";
				case DbType.DateTime: return "DateTime";
				case DbType.Decimal: return "decimal";
				case DbType.Double: return "double";
				case DbType.Guid: return "Guid";
				case DbType.Int16: return "short";
				case DbType.Int32: return "int";
				case DbType.Int64: return "long";
				case DbType.Object: return "object";
				case DbType.SByte: return "sbyte";
				case DbType.Single: return "float";
				case DbType.String: return "string";
				case DbType.StringFixedLength: return "string";
				case DbType.Time: return "TimeSpan";
				case DbType.UInt16: return "ushort";
				case DbType.UInt32: return "uint";
				case DbType.UInt64: return "ulong";
				case DbType.VarNumeric: return "decimal";
				default:
				{
					return "string";
				}
			}
		}		
		
		private string GetReaderMethod(ColumnSchema column)
		{
			switch (column.DataType)
			{
				case DbType.Byte:
				{
					return "GetByte";
				}
				case DbType.Int16:
				{
					return "GetInt16";
				}
				case DbType.Int32:
				{
					return "GetInt32";
				}
				case DbType.Int64:
				{
					return "GetInt64";
				}
				case DbType.Double:
				{
					return "GetDouble";	
				}
				case DbType.AnsiStringFixedLength:
				case DbType.AnsiString:
				case DbType.String:
				case DbType.StringFixedLength:
				{
					return "GetString";
				}
				case DbType.Boolean:
				{
					return "GetBoolean";
				}
				case DbType.Guid:
				{
					return "GetGuid";
				}
				case DbType.Currency:
				case DbType.Decimal:
				{
					return "GetDecimal";
				}
				case DbType.DateTime:
				case DbType.Date:
				{
					return "GetDateTime";
				}
				case DbType.Binary:
				{
					return "GetBytes";
				}
				default:
				{
					return "GetString";
				}
			}
		}
		
		private string GetConvertTo(ColumnSchema column)
		{
			switch (column.DataType)
			{
				case DbType.Byte:
				{
					return "Convert.ToByte";
				}
				case DbType.Int16:
				{
					return "Convert.ToInt16";
				}
				case DbType.Int32:
				{
					return "Convert.ToInt32";
				}
				case DbType.Int64:
				{
					return "Convert.ToInt64";
				}
				case DbType.Double:
				{
					return "Convert.ToDouble";
				}
				case DbType.AnsiStringFixedLength:
				case DbType.AnsiString:
				case DbType.String:
				case DbType.StringFixedLength:
				{
					return "";
				}
				case DbType.Boolean:
				{
					return "Convert.ToBoolean";
				}
				case DbType.Guid:
				{
					return "new Guid";
				}
				case DbType.Currency:
				case DbType.Decimal:
				{
					return "Convert.ToDecimal";
				}
				case DbType.DateTime:
				case DbType.Date:
				{
					return "Convert.ToDateTime";
				}
				case DbType.Binary:
				{
					return "byte[]";
				}
				default:
				{
					return "";
				}
			}
		}
		
		public string GetSqlDbType(ColumnSchema column)
		{
			switch (column.NativeType)
			{
				case "bigint": return "BigInt";
				case "binary": return "Binary";
				case "bit": return "Bit";
				case "char": return "Char";
				case "datetime": return "DateTime";
				case "decimal": return "Decimal";
				case "float": return "Float";
				case "image": return "Image";
				case "int": return "Int";
				case "money": return "Money";
				case "nchar": return "NChar";
				case "ntext": return "NText";
				case "numeric": return "Decimal";
				case "nvarchar": return "NVarChar";
				case "real": return "Real";
				case "smalldatetime": return "SmallDateTime";
				case "smallint": return "SmallInt";
				case "smallmoney": return "SmallMoney";
				case "sql_variant": return "Variant";
				case "sysname": return "NChar";
				case "text": return "Text";
				case "timestamp": return "Timestamp";
				case "tinyint": return "TinyInt";
				case "uniqueidentifier": return "UniqueIdentifier";
				case "varbinary": return "VarBinary";
				case "varchar": return "VarChar";
				default: return "NVarChar";
			}
		}

		public string GetDbType(ColumnSchema column)
		{
			switch (column.NativeType)
			{
				case "bigint": return "Int64";
				case "binary": return "Binary";
				case "bit": return "Boolean";
				case "char": return "String";
				case "datetime": return "Date";
				case "decimal": return "Decimal";
				case "float": return "Float";
				case "image": return "Binary";
				case "int": return "Int32";
				case "money": return "Decimal";
				case "nchar": return "string";
				case "ntext": return "string";
				case "numeric": return "Decimal";
				case "nvarchar": return "String";
				case "real": return "Decimal";
				case "smalldatetime": return "Date";
				case "smallint": return "Int16";
				case "smallmoney": return "Decimal";
				case "sql_variant": return "Object";
				case "sysname": return "String";
				case "text": return "String";
				case "timestamp": return "DateTime";
				case "tinyint": return "Byte";
				case "uniqueidentifier": return "Guid";
				case "varbinary": return "Binary";
				case "varchar": return "String";
				default: return "Object";
			}
		}
		
		public string GetSqlDbType(ParameterSchema parameter)
		{
			switch (parameter.NativeType)
			{
				case "bigint": return "BigInt";
				case "binary": return "Binary";
				case "bit": return "Bit";
				case "char": return "Char";
				case "datetime": return "DateTime";
				case "decimal": return "Decimal";
				case "float": return "Float";
				case "image": return "Image";
				case "int": return "Int";
				case "money": return "Money";
				case "nchar": return "NChar";
				case "ntext": return "NText";
				case "numeric": return "Decimal";
				case "nvarchar": return "NVarChar";
				case "real": return "Real";
				case "smalldatetime": return "SmallDateTime";
				case "smallint": return "SmallInt";
				case "smallmoney": return "SmallMoney";
				case "sql_variant": return "Variant";
				case "sysname": return "NChar";
				case "text": return "Text";
				case "timestamp": return "Timestamp";
				case "tinyint": return "TinyInt";
				case "uniqueidentifier": return "UniqueIdentifier";
				case "varbinary": return "VarBinary";
				case "varchar": return "VarChar";
				default: return "NVarChar";
			}
		}
		
		public string GetSqlDbType(string nativeType)
		{
			switch (nativeType)
			{
				case "bigint": return "BigInt";
				case "binary": return "Binary";
				case "bit": return "Bit";
				case "char": return "Char";
				case "datetime": return "DateTime";
				case "decimal": return "Decimal";
				case "float": return "Float";
				case "image": return "Image";
				case "int": return "Int";
				case "money": return "Money";
				case "nchar": return "NChar";
				case "ntext": return "NText";
				case "numeric": return "Decimal";
				case "nvarchar": return "NVarChar";
				case "real": return "Real";
				case "smalldatetime": return "SmallDateTime";
				case "smallint": return "SmallInt";
				case "smallmoney": return "SmallMoney";
				case "sql_variant": return "Variant";
				case "sysname": return "NChar";
				case "text": return "Text";
				case "timestamp": return "Timestamp";
				case "tinyint": return "TinyInt";
				case "uniqueidentifier": return "UniqueIdentifier";
				case "varbinary": return "VarBinary";
				case "varchar": return "VarChar";
				default:
				{
					return "NVarChar";
				}
			}
		}		
		
		public string GetEntityName()
		{
			string entityName = SourceTable.Name;
			/*
			int pos = SourceTable.Name.LastIndexOf('_');
			if (pos > 0)
			{
				entityName = SourceTable.Name.Substring(pos + 1);
			}
			*/
			if (entityName.StartsWith(TablePrefix))
			{
				entityName = entityName.Substring(TablePrefix.Length);
			}
			
			if (entityName.EndsWith("Movies"))
			{
				entityName = entityName.Substring(0, entityName.Length - 3);
				entityName += "ie";
			}

			else if (entityName.EndsWith("Status"))
			{
				entityName = entityName.Substring(0, entityName.Length - 3);
				entityName += "tus";
			}

			else if (entityName.EndsWith("ies"))
			{
				entityName = entityName.Substring(0, entityName.Length - 3);
				entityName += "y";
			}
			else if (entityName.EndsWith("s"))
			{
				entityName = entityName.Substring(0, entityName.Length - 1);
			}
			

			return entityName;
		}	
        
        public bool ColumnIsIdentity(ColumnSchema column)
		{
			return (((bool)column.ExtendedProperties["CS_IsIdentity"].Value) == true);
		}        

        public string GetColumnDescription(ColumnSchema column)
		{
			return column.ExtendedProperties["CS_Description"].Value.ToString();
		} 
        
		public bool IsAutoNumberPrimaryKeyColumn(ColumnSchema col)
		{
			if (col.IsPrimaryKeyMember && ColumnIsIdentity(col))	
			{
				return true;	
			}
			return false;
		}
		
		public bool IsHasAutoNumberPrimaryKey(ColumnSchemaCollection cols)
		{
			foreach (ColumnSchema col in cols)
			{
				if (col.IsPrimaryKeyMember && ColumnIsIdentity(col))	
				{
					return true;	
				}
			}
			return false;
		}
				
		public bool IsHasMultiPrimaryKey(ColumnSchemaCollection cols)
		{
            int cnt = 0;
			foreach (ColumnSchema col in cols)
			{
				if (col.IsPrimaryKeyMember)	
				{
					cnt += 1;	
				}
			}			
            return (cnt > 0);
		}
        
		public bool IsHasUniqueColumn(ColumnSchemaCollection cols)
		{
			foreach (ColumnSchema col in cols)
			{
				if (col.IsUnique)	
				{
					return true;	
				}
			}
			return false;
		}
        
		public string GetCSharpPrimaryKeyVariableType(TableSchema table)
		{		
			ColumnSchemaCollection cols = table.Columns;
			foreach (ColumnSchema col in cols)
			{				
				if (col.IsPrimaryKeyMember && col.IsUnique && (col.DataType == DbType.Int16 || col.DataType == DbType.Int32 || col.DataType == DbType.Int64))	
				{					
					return GetCSharpVariableType(col);
				}
			}
			return "int";
		}
		
		public string GetCSharpPrimaryKey(ColumnSchemaCollection columns)
		{		           
			foreach (ColumnSchema col in columns)
			{				
				if (col.IsPrimaryKeyMember && col.IsUnique)	
				{					
					return col.Name;
				}
			}

			return "ID";
		}
        
		public string GetCSharpPrimaryKeyIdentity(ColumnSchemaCollection columns)
		{		           
			foreach (ColumnSchema col in columns)
			{				
				if (IsAutoNumberPrimaryKeyColumn(col))	
				{					
					return col.Name;
				}
			}

			return "ID";
		}
		        
		public string GetStoredProcedurePrefix()
		{
			//int pos = this.SourceTable.Name.LastIndexOf('_');
			//return "p" + this.SourceTable.Name.Substring(1, pos);
            
            return StoredProcedurePrefix;
		}
		
		// AddEditItem
		public string GetLoadItem()
		{
			string result = string.Empty;
			if (!this.IsHasAutoNumberPrimaryKey(SourceTable.Columns))
			{
				result += "if (";
				foreach (ColumnSchema col in SourceTable.Columns)
				{
					if (col.IsPrimaryKeyMember)
					{
						result += string.Format("(string.IsNullOrEmpty(Request.QueryString[\"{0}\"]) == false) && ", col.Name);
					}
				}
				result = result.Remove(result.Length - 4, 4);
				result += ")";
				return result;
			}
			return "if (this.ItemID > 0)";
		}		
				
		public string GetLabelControl(ColumnSchema col)
		{
			string colname = col.Name;
			if (col.Name.Length > 2)
			{
				colname = col.Name.Replace("ID", "");
			}
			string result = string.Empty;			
			result = string.Format("<asp:Label ID=\"lbl{0}\" runat=\"server\" Text=\"{1}:\" />", col.Name, string.IsNullOrEmpty(col.Description) ? col.Name : col.Description);
			return result;
		}
		
		public string GetValidateControl(ColumnSchema col)
		{
			string colname = col.Name;
			if (col.Name.Length > 2)
			{
				colname = col.Name.Replace("ID", "");
			}
			string ControlToValidate = "txt" + col.Name;
			if (col.IsForeignKeyMember)
			{
				ControlToValidate = "cb" + colname;
			}
			
			string result = string.Empty;
            if (col.SystemType == typeof(System.DateTime))
            {
                result += string.Format("<ajax:CalendarExtender ID=\"txt{0}_CalendarExtender\" runat=\"server\" Enabled=\"True\" TargetControlID=\"txt{0}\" />", col.Name);
                result += string.Format("<ajax:MaskedEditExtender ID=\"txt{0}_MaskedEditExtender\" runat=\"server\" Enabled=\"True\" Mask=\"99/99/9999\" MaskType=\"Date\" TargetControlID=\"txt{0}\" />", col.Name);
                result += string.Format("<asp:CompareValidator ID=\"cv{0}\" runat=\"server\" CssClass=\"Error\" Display=\"Dynamic\" ErrorMessage='<br />\"{1}\" khọng hợp lệ' Text='<br />\"{1}\" không hợp lệ' ControlToValidate=\"{2}\" SetFocusOnError=\"True\" Operator=\"DataTypeCheck\" Type=\"Date\" />", col.Name, string.IsNullOrEmpty(col.Description) ? col.Name : col.Description, ControlToValidate);
            }
            else if (col.SystemType == typeof(System.Int16) || col.SystemType == typeof(System.Int32) || col.SystemType == typeof(System.Int64) || col.SystemType == typeof(System.Byte))
            {
                result += string.Format("<asp:CompareValidator ID=\"cv{0}\" runat=\"server\" CssClass=\"Error\" Display=\"Dynamic\" ErrorMessage='\"{1}\" không hợp lệ' Text='<br />\"{1}\" không hợp lệ' ControlToValidate=\"{2}\" SetFocusOnError=\"True\" Operator=\"DataTypeCheck\" Type=\"Integer\" />", col.Name, string.IsNullOrEmpty(col.Description) ? col.Name : col.Description, ControlToValidate);  
            }
            
			if (col.AllowDBNull == false)
			{
				result += string.Format("<asp:RequiredFieldValidator ID=\"rfv{0}\" runat=\"server\" CssClass=\"Error\" Display=\"Dynamic\" ErrorMessage='Bạn chưa nhập \"{1}\"' Text='<br />Bạn chưa nhập \"{1}\"' ControlToValidate=\"{2}\" SetFocusOnError=\"True\" />", col.Name, string.IsNullOrEmpty(col.Description) ? col.Name : col.Description, ControlToValidate);
			}
            
			return result;
		}
		
        public string GetValidateControl_English(ColumnSchema col)
		{
			string colname = col.Name;
			if (col.Name.Length > 2)
			{
				colname = col.Name.Replace("ID", "");
			}
			string ControlToValidate = "txt" + col.Name;
			if (col.IsForeignKeyMember)
			{
				ControlToValidate = "cb" + colname;
			}
			
			string result = string.Empty;
			if (col.AllowDBNull == false)
			{
				result = string.Format("<asp:RequiredFieldValidator ID=\"rfv{0}\" runat=\"server\" CssClass=\"Error\" Display=\"Dynamic\" ErrorMessage='<br />You must enter \"{1}\"' Text='<br />You must enter \"{1}\"' ControlToValidate=\"{2}\" SetFocusOnError=\"True\" />", col.Name, string.IsNullOrEmpty(col.Description) ? col.Name : col.Description, ControlToValidate);
			}
            if (col.SystemType == typeof(System.DateTime))
            {
                result += string.Format("<asp:CompareValidator ID=\"rfv{0}\" runat=\"server\" CssClass=\"Error\" Display=\"Dynamic\" ErrorMessage='<br />\"{1}\" is invalid' Text='<br />\"{1}\" is invalid' ControlToValidate=\"{2}\" SetFocusOnError=\"True\" Operator=\"DataTypeCheck\" Type=\"Date\" />", col.Name, string.IsNullOrEmpty(col.Description) ? col.Name : col.Description, ControlToValidate);
            }
			return result;
		}
        
		public string GetSafeHtmlValidateControl(ColumnSchema col)
		{
			string colname = col.Name;
			if (col.Name.Length > 2)
			{
				colname = col.Name.Replace("ID", "");
			}
			string ControlToValidate = "txt" + col.Name;
			if (col.IsForeignKeyMember)
			{
				ControlToValidate = "cb" + colname;
			}
			
			string result = string.Empty;
			if (col.DataType == DbType.String || col.DataType == DbType.StringFixedLength || col.DataType == DbType.AnsiString || col.DataType == DbType.AnsiStringFixedLength)
			{
				result = string.Format("<asp:RegularExpressionValidator ID=\"rev{0}\" runat=\"server\" ControlToValidate=\"{1}\" Display=\"Dynamic\" ErrorMessage='\"{2}\" không hợp lệ' Text='\"{2}\" không hợp lệ' SetFocusOnError=\"True\" ValidationExpression=\"<%$ Resources:Strings, VALIDATE_SAFE_HTML_EXPRESSION %>\" />", col.Name, ControlToValidate, colname);
			}
			return result;
		}
		
		public string GetControl(ColumnSchema col)
		{
			string colname = col.Name;
			if (col.Name.Length > 2)
			{
				colname = col.Name.Replace("ID", "");
			}
			string result = string.Empty;
			if (col.IsForeignKeyMember)
			{
				result = string.Format("<asp:DropDownList ID=\"ddl{0}\" runat=\"server\" DataTextField=\"Title\" DataValueField=\"ID\" />", colname);
			}
			else
			{
				if (col.SystemType == typeof(System.String))
				{
					result = string.Format("<asp:TextBox ID=\"txt{0}\" runat=\"server\" Width=\"300\" MaxLength=\"{1}\" />", colname, col.Size < 0 ? 0 : col.Size);
					if (col.Size >= 512 || col.Size == -1)
					{
						result = string.Format("<asp:TextBox ID=\"txt{0}\" runat=\"server\" TextMode=\"MultiLine\" Rows=\"5\" Width=\"300\" MaxLength=\"{1}\" />", colname, col.Size < 0 ? 0 : col.Size);
					}
				}
				else if (col.SystemType == typeof(System.Boolean))
				{
					result = string.Format("<asp:CheckBox ID=\"chk{0}\" runat=\"server\" />", colname);
				}
                else if (col.SystemType == typeof(System.DateTime))
                {
                    result = string.Format("<asp:TextBox ID=\"txt{0}\" runat=\"server\"  Width=\"100\" MaxLength=\"10\" />", colname);
                }
				else 
				{
					result = string.Format("<asp:TextBox ID=\"txt{0}\" runat=\"server\"  Width=\"300\" />", colname);
				}
			}
			return result;
		}
		
		public string GetControlValueInCode(ColumnSchema col)
		{
			string colname = col.Name;
			if (col.Name.Length > 2)
			{
				colname = col.Name.Replace("ID", "");
			}
			string result = string.Empty;
			if (col.IsForeignKeyMember)
			{
				if (col.SystemType != typeof(System.String))
				{
					result = this.GetConvertTo(col) +  "(ddl" + col.Name + ".SelectedValue)";
				}
				else
				{
					result = "ddl" + col.Name + ".SelectedValue";	
				}
			}
			else
			{
				if (col.Name == "ModuleID")
				{
					result = "this.ModuleID";
				}
				else
				{
					if (col.SystemType != typeof(System.String) && col.SystemType != typeof(System.Xml.XmlDocument))
					{
						if (col.SystemType == typeof(System.Boolean))
						{
							result =  this.GetConvertTo(col) + "(chk" + col.Name + ".Checked)";
						}
						else
						{
							result =  this.GetConvertTo(col) + "(txt" + col.Name + ".Text)";
						}
					}
					else
					{						
						result =  "txt" + col.Name + ".Text";
					}
				}
			}
			//result = col.SystemType.ToString();
			return result;
		}
	}
}